package com.querie.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.querie.model.beans.Query;
import com.querie.model.beans.QueryLog;
import com.querie.sql.BaseDAO;
import com.querie.sql.ConnectionFactory;
import com.querie.sql.PreparedStatementSetter;
import com.querie.sql.ResultSetExtractor;

public  class QueryDAOImpl extends BaseDAO implements QueryDAO{
	public Query load(final long sessionid, final long queryid) throws Exception{
		String sql = "SELECT * FROM query WHERE sessionid=? and queryid=?";
		return (Query) getRow(sql, new PreparedStatementSetter() {	
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setLong(1, sessionid);
				ps.setLong(2, queryid);
			}
		}, new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
			Query row  = new Query();
				row.setSessionid(rs.getLong("sessionid"));
				row.setQueryid(rs.getLong("queryid"));
				row.setColumnfragid(rs.getString("columnfragid"));
				row.setTablefragid(rs.getString("tablefragid"));
				row.setWherefragid(rs.getString("wherefragid"));
				row.setGroupfragid(rs.getString("groupfragid"));
				row.setHavingfragid(rs.getString("havingfragid"));
				row.setOrderbyfragid(rs.getString("orderfragid"));
				row.setQueryfragmentimportance(rs.getString("queryfragmentimportance"));
				return row;
			}
	});
	}

	
	public Query load(final long queryid) throws Exception{
		String sql = "SELECT * FROM query WHERE queryid=?";
		return (Query) getRow(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setLong(1, queryid);
			}
		}, new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
			Query row  = new Query();
				row.setSessionid(rs.getLong("sessionid"));
				row.setQueryid(rs.getLong("queryid"));
				row.setColumnfragid(rs.getString("columnfragid"));
				row.setTablefragid(rs.getString("tablefragid"));
				row.setWherefragid(rs.getString("wherefragid"));
				row.setGroupfragid(rs.getString("groupfragid"));
				row.setHavingfragid(rs.getString("havingfragid"));
				row.setOrderbyfragid(rs.getString("orderfragid"));
				row.setQueryfragmentimportance(rs.getString("queryfragmentimportance"));
				return row;
			}
	});
	}
	
	public int delete(final long sessionid, final long queryid) throws Exception{
		String sql = "DELETE FROM query WHERE sessionid=? and queryid=?";
		return executeUpdate(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setLong(1, sessionid);
				ps.setLong(2, queryid);
			}
		});
	}

	public int deleteAll() throws Exception{
		String sql = "DELETE FROM query";
		return executeUpdate(sql, null);
	}
	public int update(final Query data) throws Exception{
		String sql = "UPDATE query SET columnfragid = ?, tablefragid = ?, wherefragid = ?, groupfragid = ?, havingfragid = ?, orderfragid = ? queryfragmentimportance = ? WHERE sessionid=? and queryid=?";
		return executeUpdate(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setString(1, data.getColumnfragid());
				ps.setString(2, data.getTablefragid());
				ps.setString(3, data.getWherefragid());
				ps.setString(4, data.getGroupfragid());
				ps.setString(5, data.getHavingfragid());
				ps.setString(6, data.getOrderbyfragid());
				ps.setString(7, data.getQueryfragmentimportance());
				ps.setLong(8, data.getSessionid());
				ps.setLong(9, data.getQueryid());
			}
	});
	}

	public long insert(final Query data) throws Exception{
		String sql = "INSERT INTO query (queryid, columnfragid, tablefragid, wherefragid, groupfragid, havingfragid, orderfragid, sessionid, queryfragmentimportance) VALUES (?,?, ?, ?, ?, ?, ?, ?,? )";
		long id = executeInsert(sql, new PreparedStatementSetter() {
			public void prepare(PreparedStatement ps) throws SQLException {
				ps.setLong(1, data.getQueryid());
				ps.setString(2, data.getColumnfragid());
				ps.setString(3, data.getTablefragid());
				ps.setString(4, data.getWherefragid());
				ps.setString(5, data.getGroupfragid());
				ps.setString(6, data.getHavingfragid());
				ps.setString(7, data.getOrderbyfragid());
				ps.setLong(8, data.getSessionid());
				ps.setString(9, data.getQueryfragmentimportance());
			}
		});
		data.setSessionid(id);
		return id;
	}

	public List<Query> queryAll()throws Exception{
		String sql = "SELECT * FROM query";
		return (List<Query>) getList(sql, new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
				Query row = new Query();
				row.setSessionid(rs.getLong("sessionid"));
				row.setQueryid(rs.getLong("queryid"));
				row.setColumnfragid(rs.getString("columnfragid"));
				row.setTablefragid(rs.getString("tablefragid"));
				row.setWherefragid(rs.getString("wherefragid"));
				row.setGroupfragid(rs.getString("groupfragid"));
				row.setHavingfragid(rs.getString("havingfragid"));
				row.setOrderbyfragid(rs.getString("orderfragid"));
				row.setQueryfragmentimportance(rs.getString("queryfragmentimportance"));
				return row;
			}
	});
	}

	public List<Query> queryAll(long sessionid)throws Exception{
		String sql = "SELECT * FROM query where sessionid="+sessionid;
		return (List<Query>) getList(sql, new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
				Query row = new Query();
				row.setSessionid(rs.getLong("sessionid"));
				row.setQueryid(rs.getLong("queryid"));
				row.setColumnfragid(rs.getString("columnfragid"));
				row.setTablefragid(rs.getString("tablefragid"));
				row.setWherefragid(rs.getString("wherefragid"));
				row.setGroupfragid(rs.getString("groupfragid"));
				row.setHavingfragid(rs.getString("havingfragid"));
				row.setOrderbyfragid(rs.getString("orderfragid"));
				row.setQueryfragmentimportance(rs.getString("queryfragmentimportance"));
				return row;
			}
	});
	}
	 public ResultSetExtractor getListResultSetExtractor(){
		return new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
				Query row = new Query();
				row.setSessionid(rs.getLong("sessionid"));
				row.setQueryid(rs.getLong("queryid"));
				row.setColumnfragid(rs.getString("columnfragid"));
				row.setTablefragid(rs.getString("tablefragid"));
				row.setWherefragid(rs.getString("wherefragid"));
				row.setGroupfragid(rs.getString("groupfragid"));
				row.setHavingfragid(rs.getString("havingfragid"));
				row.setOrderbyfragid(rs.getString("orderfragid"));
				row.setQueryfragmentimportance(rs.getString("queryfragmentimportance"));
				return row;
			}
	};
	}

	public List<Query> queryAllWithPage(int startOfPage, int... pageSize)
			throws Exception {
		int page = 20;
		if (pageSize.length > 0)
		{
			page = pageSize[0];
		}
		
		String sql = "SELECT * FROM Query LIMIT "+startOfPage+" ,"+page ;
		return (List) getList(sql, new ResultSetExtractor() {
			public Object extract(ResultSet rs) throws SQLException {
				Query row = new Query();
				row.setSessionid(rs.getLong("sessionid"));
				row.setQueryid(rs.getLong("queryid"));
				row.setColumnfragid(rs.getString("columnfragid"));
				row.setTablefragid(rs.getString("tablefragid"));
				row.setWherefragid(rs.getString("wherefragid"));
				row.setGroupfragid(rs.getString("groupfragid"));
				row.setHavingfragid(rs.getString("havingfragid"));
				row.setOrderbyfragid(rs.getString("orderfragid"));
				row.setQueryfragmentimportance(rs.getString("queryfragmentimportance"));
				return row;
			}
	});
	}
	
	public int countAll() throws Exception
	{
		   Connection c = null;
		    PreparedStatement ps = null;
		    ResultSet rs = null;
		    int count = 0;
		    try {
		      c = ConnectionFactory.getConnection();
		      ps = c.prepareStatement("SELECT COUNT(*) FROM Query");
		      rs = ps.executeQuery();
		      while (rs.next()) {
		    	  count = rs.getInt(1);
		      }
		      
		    } catch (Exception e) {
		    	throw e;
		    }
		    return count;
	}

	}